#include <stdio.h>
#include <string.h>
#include "CIpodLog.h"
#include "CIap2Sqlite.h"
#include "util/pinyin.h"
#include "util/CStrTool.h"

//#define SQLITE_USE
CIap2Sqlite::CIap2Sqlite() {
    m_Handle = NULL;
    pthread_mutex_init(&m_SqliteMutex, NULL);
}

CIap2Sqlite::~CIap2Sqlite()
{
    CIpodLog::e("release sqlite resources.\n");

    pthread_mutex_destroy(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
#endif
}

int CIap2Sqlite::open(std::string path) {
    int ret = 0;
    if (path.empty()) {
        CIpodLog::e("CIpodSqlite::open, the path is null!\n");
        return -1;
    }
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    char libraryPath[128];
    sprintf(libraryPath, "%s/%s", path.data(), MEDIA_LIBRARY_NAME);

    if( sqlite3_open( libraryPath, &m_Handle ) != SQLITE_OK )
    {
        CIpodLog::e("CIpodSqlite::open, open library error: %s! The path is : %s\n", sqlite3_errmsg(m_Handle), libraryPath);
        ret = -1;
        goto exit;
    }

    sqlite3_exec(m_Handle,"PRAGMA synchronous = OFF",NULL, NULL, NULL);

    char sql[1024];
    //mediaitem
    sprintf(sql,
            "create table if not exists %s("
            "%s TEXT PRIMARY KEY, " // ITEM_ID
            "%s TEXT, "             // ITEM_TITLE
            "%s INTEGER, "          // ITEM_TYPE
            "%s INTEGER, "          // ITEM_RATING
            "%s INTEGER, "          // ITEM_DURATION
            "%s TEXT, "             // ITEM_ALBUM_ID
            "%s TEXT, "             // ITEM_ALBUM
            "%s INTEGER, "          // ITEM_ALBUM_TRACK_NUMBER
            "%s INTEGER, "          // ITEM_ALBUM_TRACK_COUNT
            "%s INTEGER, "          // ITEM_ALBUM_DISC_NUMBER
            "%s INTEGER, "          // ITEM_ALBUM_DISC_COUNT
            "%s TEXT, "             // ITEM_ARTIST_ID
            "%s TEXT, "             // ITEM_ARTIST
            "%s TEXT, "             // ITEM_ALBUM_ARTIST_ID
            "%s TEXT, "             // ITEM_ALBUM_ARTIST
            "%s TEXT, "             // ITEM_GENRE_ID
            "%s TEXT, "             // ITEM_GENRE
            "%s TEXT, "             // ITEM_COMPOSER_ID
            "%s TEXT, "             // ITEM_COMPOSER
            "%s INTEGER, "          // ITEM_IS_PART_OF_COMPILATION
            "%s INTEGER, "          // ITEM_IS_LIKE_SUPPORTED
            "%s INTEGER, "          // ITEM_IS_BAN_SUPPORTED
            "%s INTEGER, "          // ITEM_IS_LIKED
            "%s INTEGER, "          // ITEM_IS_BANNED
            "%s INTEGER, "          // ITEM_IS_RESIDENT_ON_DEVICE
            "%s INTEGER)",          // ITEM_CHAPTER_COUNT
            ITEM_TABLE,
            ITEM_ID,
            ITEM_TITLE,
            ITEM_TYPE,
            ITEM_RATING,
            ITEM_DURATION,
            ITEM_ALBUM_ID,
            ITEM_ALBUM,
            ITEM_ALBUM_TRACK_NUMBER,
            ITEM_ALBUM_TRACK_COUNT,
            ITEM_ALBUM_DISC_NUMBER,
            ITEM_ALBUM_DISC_COUNT,
            ITEM_ARTIST_ID,
            ITEM_ARTIST,
            ITEM_ALBUM_ARTIST_ID,
            ITEM_ALBUM_ARTIST,
            ITEM_GENRE_ID,
            ITEM_GENRE,
            ITEM_COMPOSER_ID,
            ITEM_COMPOSER,
            ITEM_IS_PART_OF_COMPILATION,
            ITEM_IS_LIKE_SUPPORTED,
            ITEM_IS_BAN_SUPPORTED,
            ITEM_IS_LIKED,
            ITEM_IS_BANNED,
            ITEM_IS_RESIDENT_ON_DEVICE,
            ITEM_CHAPTER_COUNT);
    if (sqlite3_exec(m_Handle, sql, NULL, NULL, NULL) != SQLITE_OK) {
        CIpodLog::e("CIpodSqlite::open, create item table failed!\n");
        ret = -1;
        goto exit;
    }

    //mediaplaylist
    memset(sql, 0,sizeof(sql));
    sprintf(sql,
            "create table if not exists %s("
            "%s TEXT PRIMARY KEY, " // PLAYLIST_ID
            "%s TEXT,"              // PLAYLIST_NAME
            "%s TEXT, "             // PLAYLIST_PARENT_ID
            "%s INTEGER,"           // PLAYLIST_IS_GENIUS_MIX
            "%s INTEGER, "          // PLAYLIST_IS_FOLDER
            "%s INTEGER, "          // PLAYLIST_IS_ITUNES_RADIO_STATION
            "%s BLOB)",             // PLAYLIST_ITEMS
            PLAYLIST_TABLE,
            PLAYLIST_ID,
            PLAYLIST_NAME,
            PLAYLIST_PARENT_ID,
            PLAYLIST_IS_GENIUS_MIX,
            PLAYLIST_IS_FOLDER,
            PLAYLIST_IS_ITUNES_RADIO_STATION,
            PLAYLIST_ITEMS);
    if (sqlite3_exec(m_Handle, sql, NULL, NULL, NULL) != SQLITE_OK) {
        CIpodLog::e("CIpodSqlite::open, create playlist table failed!\n");
        ret = -1;
        goto exit;
    }

    // 创建排序规则
    sqlite3_create_collation(m_Handle, PINYIN_NAME, SQLITE_UTF8, 0, binCollFuncUtf8);
#endif
exit:
    pthread_mutex_unlock(&m_SqliteMutex);
    return ret;
}

int CIap2Sqlite::close() {
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        ret = sqlite3_close(m_Handle);
        m_Handle = NULL;
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::beginTransaction() {
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        ret = sqlite3_exec(m_Handle, "begin transaction", NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::commitTransaction() {
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        ret = sqlite3_exec(m_Handle, "commit transaction", NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::insertMediaItem(IAP2MediaItem item) {
//	CIpodLog::i("CIap2Sqlite::insertMediaItem, -------------- \n");
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        char sql[1024] = { 0 };
        sprintf(sql,
                "insert into %s(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
                "values('%llu', '%s', '%d', '%d', '%lu', '%llu', '%s', '%d', '%d', '%d', '%d', '%llu', '%s', '%llu', '%s', '%llu', '%s', '%llu', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%d')",
                ITEM_TABLE,
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TYPE,
                ITEM_RATING,
                ITEM_DURATION,
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_ALBUM_TRACK_NUMBER,
                ITEM_ALBUM_TRACK_COUNT,
                ITEM_ALBUM_DISC_NUMBER,
                ITEM_ALBUM_DISC_COUNT,
                ITEM_ARTIST_ID,
                ITEM_ARTIST,
                ITEM_ALBUM_ARTIST_ID,
                ITEM_ALBUM_ARTIST,
                ITEM_GENRE_ID,
                ITEM_GENRE,
                ITEM_COMPOSER_ID,
                ITEM_COMPOSER,
                ITEM_IS_PART_OF_COMPILATION,
                ITEM_IS_LIKE_SUPPORTED,
                ITEM_IS_BAN_SUPPORTED,
                ITEM_IS_LIKED,
                ITEM_IS_BANNED,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_CHAPTER_COUNT,
                item.id,
                modifyString(item.title),
                item.type,
                item.rating,
                item.duration,
                item.albumID,
                modifyString(item.album),
                item.albumTrackNumber,
                item.albumTrackCount,
                item.albumDiscNumber,
                item.albumDiscCount,
                item.artistID,
                modifyString(item.artist),
                item.albumArtistID,
                modifyString(item.albumArtist),
                item.genreID,
                modifyString(item.genre),
                item.composerID,
                modifyString(item.composer),
                item.partOfCompilation,
                item.likeSupported,
                item.banSupported,
                item.liked,
                item.banned,
                item.residentOnDevice,
                item.chapterCount);
        ret = sqlite3_exec(m_Handle, sql, NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::insertMediaPlaylist(IAP2MediaPlaylist playlist)
{
    int ret = SQLITE_ERROR;

    if( m_Handle == NULL )
    {
        CIpodLog::e( "sqlite database is not exist.\n" );
        return -1;
    }
    pthread_mutex_lock(&m_SqliteMutex);

#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    char sql[1024] = { 0 };
    sprintf(sql,
            "insert into %s (%s, %s, %s, %s, %s, %s) values ('%llu', '%s', '%llu', '%d', '%d', '%d')",
            PLAYLIST_TABLE,
            PLAYLIST_ID,
            PLAYLIST_NAME,
            PLAYLIST_PARENT_ID,
            PLAYLIST_IS_GENIUS_MIX,
            PLAYLIST_IS_FOLDER,
            PLAYLIST_IS_ITUNES_RADIO_STATION,
            playlist.id,
            modifyString(playlist.name),
            playlist.parentID,
            playlist.geniusMix,
            playlist.folder,
            playlist.iTunesRadioStation);
    ret = sqlite3_exec(m_Handle, sql, NULL, NULL, NULL);

    CIpodLog::w("%s\n", sql);
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::updateMediaPlaylist(uint64 id, uint64 *items, uint32 num) {
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        char sql[1024] = { 0 };
        sqlite3_stmt *stat = NULL;
        sprintf(sql, "update %s set %s = ? where %s = '%llu'", PLAYLIST_TABLE, PLAYLIST_ITEMS, PLAYLIST_ID, id);
        sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
        sqlite3_bind_blob(stat, 1 , items, sizeof(uint64) * num, NULL);
        ret = sqlite3_step(stat);
        sqlite3_finalize(stat);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_DONE) ? 0 : -1;
}

int CIap2Sqlite::deleteMediaItem(uint64 id) {
    CIpodLog::d("CIap2Sqlite::deleteMediaItem, id == %lu \n", id);
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        char sql[1024];
        sprintf(sql, "delete from %s where %s='%llu'", ITEM_TABLE, ITEM_ID, id);
        ret = sqlite3_exec(m_Handle, sql, NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::deleteMediaPlaylist(uint64 id) {
    CIpodLog::d("CIap2Sqlite::deleteMediaPlaylist, id == %lu \n", id);
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        char sql[1024];
        sprintf(sql, "delete from %s where %s='%llu'", PLAYLIST_TABLE, PLAYLIST_ID, id);
        ret = sqlite3_exec(m_Handle, sql, NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::reset() {
	CIpodLog::i("sqlite delete table item and playlist.\n");
    int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        ret = sqlite3_exec(m_Handle, "delete from item", NULL, NULL, NULL);
        ret = sqlite3_exec(m_Handle, "delete from playlist", NULL, NULL, NULL);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

int CIap2Sqlite::updateMediaPlaylistName(IAP2MediaPlaylist playlist)
{
	int ret = SQLITE_ERROR;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    sqlite3_close( m_Handle );
    if (m_Handle != NULL) {
        char sql[1024] = { 0 };
        sqlite3_stmt *stat = NULL;
        //sprintf(sql, "update %s set %s = %s where %s = '%llu'", PLAYLIST_TABLE, PLAYLIST_NAME, modifyString(playlist.name), PLAYLIST_ID, playlist.id);
        sprintf(sql, "update playlist set name = '%s' where id = '%llu'", modifyString(playlist.name), playlist.id);
		CIpodLog::w("%s\n", sql);
		ret = sqlite3_exec(m_Handle, sql, NULL, NULL, NULL);
		//sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
        //sqlite3_bind_blob(stat, 1 , modifyString(playlist.name), sizeof(uint64) * num, NULL);
        //ret = sqlite3_step(stat);
        //sqlite3_finalize(stat);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (ret == SQLITE_OK) ? 0 : -1;
}

bool CIap2Sqlite::isMediaPlaylistExist(IAP2MediaPlaylist playlist) {
    int count = 0;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    if (m_Handle != NULL) {
        char sql[1024];
        sprintf(sql, "select count(*) from %s where %s = '%llu'", PLAYLIST_TABLE, ITEM_ID, playlist.id);
		CIpodLog::i( "%s\n", sql );
        sqlite3_stmt *stat = NULL;
        sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
        if (sqlite3_step(stat) == SQLITE_ROW) {
            count = sqlite3_column_int(stat , 0);
        }
        sqlite3_finalize(stat);
    }
	CIpodLog::i("sqlite3_column_int ( iCol = 0 ) count: %d\n", count);
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (count > 0) ? true : false;
}

bool CIap2Sqlite::isItemExist(IAP2MediaItem item) {
    int count = 0;
    pthread_mutex_lock(&m_SqliteMutex);
#ifdef SQLITE_USE
    if (m_Handle != NULL) {
        char sql[1024];
        sprintf(sql, "select count(*) from %s where %s = '%llu'", ITEM_TABLE, ITEM_ID, item.id);
        sqlite3_stmt *stat = NULL;
        sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
        if (sqlite3_step(stat) == SQLITE_ROW) {
            count = sqlite3_column_int(stat , 0);
        }
        sqlite3_finalize(stat);
    }
#endif
    pthread_mutex_unlock(&m_SqliteMutex);
    return (count > 0) ? true : false;
}

void CIap2Sqlite::getPlaylist(IAP2MediaList *mediaList) {
    char sql[1024] = { 0 };
    sprintf(sql,
            "select %s, %s from %s order by %s COLLATE PINYIN",
            PLAYLIST_ID,
            PLAYLIST_NAME,
            PLAYLIST_TABLE,
            PLAYLIST_NAME);
    getSource(sql, mediaList);
}

void CIap2Sqlite::getArtist(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ARTIST_ID,
                ITEM_ARTIST,
                ITEM_TABLE,
                ITEM_ARTIST,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ARTIST_ID,
                ITEM_ARTIST);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ARTIST_ID,
                ITEM_ARTIST,
                ITEM_TABLE,
                ITEM_ARTIST,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ARTIST_ID,
                ITEM_ARTIST);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getAlbum(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getGenre(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql, "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_GENRE_ID,
                ITEM_GENRE,
                ITEM_TABLE,
                ITEM_GENRE,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_GENRE_ID,
                ITEM_GENRE);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_GENRE_ID,
                ITEM_GENRE,
                ITEM_TABLE,
                ITEM_GENRE,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_GENRE_ID,
                ITEM_GENRE);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongs(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s == %d and %s == 1 order by %s COLLATE PINYIN",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_TITLE);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s == %d order by %s COLLATE PINYIN",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_TITLE);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getComposer(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_COMPOSER_ID,
                ITEM_COMPOSER,
                ITEM_TABLE,
                ITEM_COMPOSER,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_COMPOSER_ID,
                ITEM_COMPOSER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_COMPOSER_ID,
                ITEM_COMPOSER,
                ITEM_TABLE,
                ITEM_COMPOSER,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_COMPOSER_ID,
                ITEM_COMPOSER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getAudioBook(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::AUDIOBOOK,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID, ITEM_ALBUM);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::AUDIOBOOK,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getPodcast(IAP2MediaList *mediaList, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::PODCAST,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ALBUM,
                ITEM_TYPE,
                IAP2MediaType::PODCAST,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getiTunesRadio(IAP2MediaList *mediaList) {
    char sql[1024] = { 0 };
    sprintf(sql, "select %s, %s from %s where %s != '' and %s == 1 group by %s order by %s COLLATE PINYIN",
            PLAYLIST_ID,
            PLAYLIST_NAME,
            PLAYLIST_TABLE,
            PLAYLIST_NAME,
            PLAYLIST_IS_ITUNES_RADIO_STATION,
            PLAYLIST_ID,
            PLAYLIST_NAME);
    getSource(sql, mediaList);
}

void CIap2Sqlite::getArtistByGenre(IAP2MediaList *mediaList, uint64 genreid, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s != '' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ARTIST_ID,
                ITEM_ARTIST,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ARTIST_ID,
                ITEM_ARTIST);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s != '' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ARTIST_ID,
                ITEM_ARTIST,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ARTIST_ID,
                ITEM_ARTIST);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getAlbumByArtist(IAP2MediaList *mediaList, uint64 artistid, bool hideRemoteItems) {
	CIpodLog::i("CIap2Sqlite::getAlbumByArtist, -------getAlbumByArtist-----artistid == %d \n", artistid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
       sprintf(sql, "select %s, %s from %s where %s = '%llu' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getAlbumByGenreArtist(IAP2MediaList *mediaList, uint64 genreid, uint64 artistid, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s = '%llu' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    } else {
        sprintf(sql, "select %s, %s from %s where %s = '%llu' and %s = '%llu' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getAlbumByComposer(IAP2MediaList *mediaList, uint64 composerid, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s == %d and %s == 1 group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_COMPOSER_ID,
                composerid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s = '%llu' and %s == %d group by %s order by %s COLLATE PINYIN",
                ITEM_ALBUM_ID,
                ITEM_ALBUM,
                ITEM_TABLE,
                ITEM_COMPOSER_ID,
                composerid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_ID,
                ITEM_ALBUM);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByPlaylist(IAP2MediaList *mediaList, uint64 playlistid, bool hideRemoteItems) {
#ifdef SQLITE_USE
    int count = 0;
    char sql[1024] = { 0 };
    sprintf(sql, "select %s from %s where %s='%llu'", PLAYLIST_ITEMS, PLAYLIST_TABLE, PLAYLIST_ID, playlistid);
    sqlite3_stmt *stat = NULL;
    pthread_mutex_lock(&m_SqliteMutex);
    sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
    while (sqlite3_step(stat) == SQLITE_ROW) {
        uint32 idNum = sqlite3_column_bytes(stat, 0)/sizeof(uint64);
        uint64 *ids = (uint64 *)sqlite3_column_blob(stat,0);
        for (uint32 i = 0; i < idNum; i++) {
            if (hideRemoteItems) {
                if (isItemRemote(ids[i])) {
                    continue;
                }
            }
            IPODMusicSource source;
            std::string name;
            source.id = ids[i];
            source.index = count;
            getItemName(source.id, &source.name);
            mediaList->items.insert(std::make_pair(source.index, source));
            count++;
        }
    }
    mediaList->count = count;
    sqlite3_finalize(stat);
    pthread_mutex_unlock(&m_SqliteMutex);
#endif
}

void CIap2Sqlite::getSongsByAlbum(IAP2MediaList *mediaList, uint64 albumid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByAlbum, ------ albumid == d%\n", albumid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByArtistAlbum(IAP2MediaList *mediaList, uint64 artistid, uint64 albumid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByArtistAlbum, ------ albumid == d%\n", albumid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByGenreArtistAlbum(IAP2MediaList *mediaList, uint64 genreid, uint64 artistid, uint64 albumid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByGenreArtistAlbum, ------ artistid == d%\n", artistid);
	char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s,%s from %s where %s='%llu' and %s='%llu' and %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s,%s from %s where %s='%llu' and %s='%llu' and %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_GENRE_ID,
                genreid,
                ITEM_ARTIST_ID,
                artistid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByComposerAlbum(IAP2MediaList *mediaList, uint64 composerid, uint64 albumid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByComposerAlbum, ------ composerid == d%\n", composerid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_COMPOSER_ID,
                composerid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_COMPOSER_ID,
                composerid,
                ITEM_ALBUM_ID,
                albumid,
                ITEM_TYPE,
                IAP2MediaType::MUSIC,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByAudioBook(IAP2MediaList *mediaList, uint64 audiobookid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByAudioBook, ------ audiobookid == d%\n", audiobookid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                audiobookid,
                ITEM_TYPE,
                IAP2MediaType::AUDIOBOOK,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                audiobookid,
                ITEM_TYPE,
                IAP2MediaType::AUDIOBOOK,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByPodcast(IAP2MediaList *mediaList, uint64 podcastid, bool hideRemoteItems) {
	CIpodLog::e("CIpodSqlite::getSongsByPodcast, ------ podcastid == d%\n", podcastid);
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d and %s == 1 order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                podcastid,
                ITEM_TYPE,
                IAP2MediaType::PODCAST,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_ALBUM_TRACK_NUMBER);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s='%llu' and %s == %d order by %s",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ALBUM_ID,
                podcastid,
                ITEM_TYPE,
                IAP2MediaType::PODCAST,
                ITEM_ALBUM_TRACK_NUMBER);
    }
    getSource(sql, mediaList);
}

void CIap2Sqlite::getSongsByID(IAP2MediaList *mediaList, uint64 id, bool hideRemoteItems) {
    char sql[1024] = { 0 };
    if (hideRemoteItems) {
        sprintf(sql,
                "select %s, %s from %s where %s == %lu and %s == 1 order by %s COLLATE PINYIN",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ID,
                id,
                ITEM_IS_RESIDENT_ON_DEVICE,
                ITEM_TITLE);
    } else {
        sprintf(sql,
                "select %s, %s from %s where %s == %lu order by %s COLLATE PINYIN",
                ITEM_ID,
                ITEM_TITLE,
                ITEM_TABLE,
                ITEM_ID,
                id,
                ITEM_TITLE);
    }
    getSource(sql, mediaList);
}

int CIap2Sqlite::getSource(const char *sql, IAP2MediaList *mediaList) {
    CIpodLog::i("GetItem( sqlite3 ) sql: %s\n", sql);

    int count = 0;
#ifdef SQLITE_USE
    sqlite3_stmt *stat = NULL;
    pthread_mutex_lock(&m_SqliteMutex);
    sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
    while (sqlite3_step(stat) == SQLITE_ROW) {
        IPODMusicSource source;
        source.index = count;
        char *id = (char *)sqlite3_column_text(stat, 0);
        source.id = CStrTool::char2uint64(id);
        source.name = (char *)sqlite3_column_text(stat, 1);
        CIpodLog::d("CIap2Sqlite::getSource, index = %lu, id = %llu, name = %s\n", source.index, source.id, source.name.c_str());
        mediaList->items.insert(std::make_pair(source.index, source));
        count++;
    }
    mediaList->count = count;
    sqlite3_finalize(stat);
    pthread_mutex_unlock(&m_SqliteMutex);
#endif
    return count;
}

const char *CIap2Sqlite::modifyString(std::string str) {

    for (size_t i = 0; i < str.size(); i++) {
        if (str[i] == '\'') {
            str.insert(i, std::string("\'"));
            i++;
        }
    }
    if(str.size() <= PLAYLIST_STRING_MAX){
        return str.c_str();
    }else{
        //the string is too long which casue coredump and need to interception string
        //For android:
//        char temp[132] = {0};
//        sprintf(temp, "%.128s", str.c_str());
//        return temp;
        //For linux:
        return str.substr(0,PLAYLIST_STRING_MAX - 1).c_str();
    }
}

bool CIap2Sqlite::isItemRemote(uint64 id) {
    bool hide = false;
#ifdef SQLITE_USE
    if (m_Handle != NULL) {
        char sql[1024] = { 0 };
        sprintf(sql, "select %s from %s where %s='%llu'", ITEM_IS_RESIDENT_ON_DEVICE, ITEM_TABLE, ITEM_ID, id);
        sqlite3_stmt *stat = NULL;
        sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
        if (sqlite3_step(stat) == SQLITE_ROW) {
            int ret = sqlite3_column_int(stat, 0);
            if (ret == 0)  {
                hide = true;
            }
        }
        sqlite3_finalize(stat);
    }
#endif
    return hide;
}

void CIap2Sqlite::getItemName(uint64 id, std::string *name) {
#ifdef SQLITE_USE
    sqlite3_stmt *stat = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "select %s from %s where %s = '%llu'", ITEM_TITLE, ITEM_TABLE, ITEM_ID, id);
    sqlite3_prepare(m_Handle, sql, -1, &stat, 0);
    while (sqlite3_step(stat) == SQLITE_ROW) {
        char *tempName = (char *)sqlite3_column_text(stat, 0);
        *name = tempName;
    }
    sqlite3_finalize(stat);
#endif
}
